﻿using Dapper;
using Oracle.DataAccess.Client;
using RockBus.DataContracts;
using RockBus.DataContracts.ConfigurationService;
using RockBus.Logging.Interfaces;
using RockBus.ConfigurationRepository.Interfaces;
using RockBus.Utilities;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Transactions;

namespace RockBus.ConfigurationRepository.Dapper.Oracle
{
    public class DbConfigurationRepositoryConfiguration : IDbConfigurationRepositoryConfiguration
    {
        public DbConfigurationRepositoryConfiguration()
        {
            this.ConnectionString = ConfigurationManager.ConnectionStrings["RockBusOracleDb"].ConnectionString;
        }

        public string ConnectionString { get; private set; }
    }

    public class ConfigurationRepository : IConfigurationRepository
    {
        public ConfigurationRepository(ILogger logger, IDbConfigurationRepositoryConfiguration configuration)
        {
            this.Logger = logger;
            this.Configuration = configuration;
        }

        public ILogger Logger { get; set; }

        public IDbConfigurationRepositoryConfiguration Configuration { get; set; }

        public void ClearAllClients()
        {
            this.RemoveAllSubscribers(new RemoveAllSubscribersRequest());
            this.RemoveAllPublishers(new RemoveAllPublishersRequest());
        }

        public void RemoveAllSubscribers(RemoveAllSubscribersRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);

            string dbCmdSel = "select c.DbId " +
                              "from Client c " +
                              "where c.ClientTypeDbId = 0 ";
            IEnumerable<long> clientList = dbConn.Query<long>(dbCmdSel);
            foreach (var clientDbId in clientList)
            {
                ClientDelete(dbConn, clientDbId);
            }

            return;
        }

        public void RemoveAllPublishers(RemoveAllPublishersRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);

            string dbCmdSel = "select c.DbId " +
                              "from Client c " +
                              "where c.ClientTypeDbId = 1 ";
            IEnumerable<dynamic> clientList = dbConn.Query(dbCmdSel);
            foreach (var client in clientList)
            {
                ClientDelete(dbConn, client.DbId);
            }

            return;
        }

        public void ClientDelete(IDbConnection dbConn, long clientDbId)
        {
            SubscriptionDeleteBySubscriberDbId(dbConn, clientDbId);

            string dbCmdDel = "delete ReceivePort " +
                              "where PublisherDbId = @PublisherDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDel, new {PublisherDbId = clientDbId});

            string dbCmdDelNsp = "delete NamespaceDefinition " +
                                 "where ClientDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelNsp, new {ClientDbId = clientDbId});

            string dbCmdDelFil = "delete Filter " +
                                 "where ClientDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelFil, new {ClientDbId = clientDbId});

            string dbCmdDelPip = "delete Pipeline " +
                                 "where ClientDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelPip, new {ClientDbId = clientDbId});

            string dbCmdDelEnd = "delete Endpoint " +
                                 "where ClientDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelEnd, new {ClientDbId = clientDbId});

            string dbCmdDelRes = "delete Response " +
                                 "where PublisherDbId = @PublisherDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelRes, new {PublisherDbId = clientDbId});

            string dbCmdDelPub = "delete Publisher " +
                                 "where PublisherDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelPub, new {ClientDbId = clientDbId});

            string dbCmdDelSub = "delete Subscriber " +
                                 "where SubscriberDbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelSub, new {ClientDbId = clientDbId});

            string dbCmdDelCli = "delete Client " +
                                 "where DbId = @ClientDbId ";
            dbConn.ExecuteOracleSafe(dbCmdDelCli, new {ClientDbId = clientDbId});
        }

        public void SubscriptionDeleteBySubscriberDbId(IDbConnection dbConn, long ClientDbId)
        {
            string dbCmdSel = "select s.DbId " +
                              "from Subscription s " +
                              "where s.SubscriberDbId = @SubscriberDbId";

            string dbCmdDel = "delete Subscription_Filter " +
                              "where SubscriptionDbId in (" + dbCmdSel + ") ";

            string dbCmdDlt = "delete Subscription " +
                              "where SubscriberDbId = @SubscriberDbId ";

            dbConn.ExecuteOracleSafe(dbCmdDel, new {SubscriberDbId = ClientDbId});
            dbConn.ExecuteOracleSafe(dbCmdDlt, new {SubscriberDbId = ClientDbId});
        }

        public void AddOrUpdateSubscriberConfiguration(SubscriberConfiguration subscriberConfiguration)
        {
            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();

                foreach (SubscriberInfo si in subscriberConfiguration.EvaluationElementDictionary.Values.ToArray())
                {
                    // Subscriber
                    long subscriberDbId = SubscriberInsertUpdate(dbConn, si.Id);

                    // Namespaces
                    IDictionary<string, long> niIdMap = this.NamespaceDefinitionsInsertUpdate(dbConn, subscriberDbId,
                        si.NamespaceInfoDictionary.Values);

                    // Filters
                    IDictionary<string, long> fiIdMap = this.FiltersInsertUpdate(dbConn, subscriberDbId,
                        si.FilterInfoDictionary.Values);

                    // Endpoints
                    IDictionary<string, long> eiIdMap = this.EndpointsInsertUpdate(dbConn, subscriberDbId,
                        si.EndpointInfoDictionary.Values);

                    // Pipelines
                    IDictionary<string, long> piIdMap = this.PipelinesInsertUpdate(dbConn, subscriberDbId,
                        si.PipelineInfoDictionary.Values);

                    // SubscriptionInfos
                    foreach (KeyValuePair<string, SubscriptionInfo> kvp in si.InfoDictionary)
                    {
                        // Subscription
                        SubscriptionInfo sci = kvp.Value;
                        long subscriptionDbId = this.SubscriptionInsertUpdate(dbConn, subscriberDbId, sci);

                        // Subscription Filter
                        foreach (FilterInfo fi in sci.GetFilterInfoEnumerator())
                        {
                            long filterDbId = fiIdMap[fi.Id];
                            this.SubscriptionFilterInsertUpdate(dbConn, subscriptionDbId, filterDbId);
                        }
                    }
                }
            }

            return;
        }

        private long SubscriberInsertUpdate(IDbConnection dbConn, string subscriberId)
        {
            long ClientDbId = ClientInsertUpdate(dbConn, subscriberId);

            string dbCmdUpd = "update Subscriber " +
                              "set SubscriberDbId = @ClientDbId " +
                              "where SubscriberDbId = @ClientDbId ";
            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, new {ClientDbId = ClientDbId});

            if (rowCount == 0)
            {
                string dbCmdIns = "insert into Subscriber (SubscriberDbId) " +
                                  "values (@ClientDbId) ";
                //"returning SubscriberDbId into @SubscriberDbId";

                dbConn.ExecuteOracleSafe(dbCmdIns, new {ClientDbId = ClientDbId});
            }

            return ClientDbId;
        }

        private long ClientInsertUpdate(IDbConnection dbConn, string subscriberId)
        {
            string dbCmdUpd = "update Client " +
                              "set Id                      = @Id " +
                              "   ,ClientTypeDbId          = @ClientTypeDbId " +
                              "   ,ModificationDateTimeUtc = current_timestamp " +
                              "where Id = @Id ";
            object prmts = new
            {
                Id = subscriberId,
                ClientTypeDbId = (long) ClientTypeEnum.Subscriber
            };
            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, prmts);

            long ClientDbId = 0;
            if (rowCount == 1)
            {
                string dbCmdSel = "select c.DbId " +
                                  "from Client c " +
                                  "where c.Id = @Id";
                ClientDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new {Id = subscriberId}).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Client (Id " +
                                  "                   ,ClientTypeDbId " +
                                  "                   ,ModificationDateTimeUtc) " +
                                  "values (@Id " +
                                  "       ,@ClientTypeDbId " +
                                  "       ,current_timestamp) " +
                                  "returning DbId into @DbId";

                ClientDbId = dbConn.QueryReturning<long>(dbCmdIns, prmts, "DbId");
            }
            return ClientDbId;
        }

        private long SubscriptionInsertUpdate(IDbConnection dbConn, long subscriberDbId,
            SubscriptionInfo subscriptionInfo)
        {
            string dbCmd = "update Subscription " +
                           "set Name                        = @Name " +
                           "   ,EndpointId                  = @EndpointId " +
                           "   ,Persistent                  = @Persistent " +
                           "   ,DeliveryAttemptIntervalSecs = @DeliveryAttemptIntervalSecs " +
                           "   ,MaxDeliveryAttemptCount     = @MaxDeliveryAttemptCount " +
                           "   ,Paused                      = @Paused " +
                           "   ,ExpiryDateTimeUtc           = @ExpiryDateTimeUtc " +
                           "   ,MessageValidityDurationSecs = @MessageValidityDurationSecs " +
                           "   ,RequestPipelineId           = @RequestPipelineId " +
                           "   ,ResponsePipelineId          = @ResponsePipelineId " +
                           "where Id             = @Id " +
                           "  and SubscriberDbId = @SubscriberDbId";

            string reqPlId = null;
            if (!string.IsNullOrEmpty(subscriptionInfo.Detail.RequestPipelineId))
            {
                reqPlId = subscriptionInfo.Detail.RequestPipelineId;
            }

            string respPlId = null;
            if (!string.IsNullOrEmpty(subscriptionInfo.Detail.ResponsePipelineId))
            {
                respPlId = subscriptionInfo.Detail.ResponsePipelineId;
            }

            object parameters = new
            {
                Id = subscriptionInfo.Detail.Id,
                Name = subscriptionInfo.Detail.Name,
                EndpointId = subscriptionInfo.Detail.EndpointId,
                Persistent = Convertor.BooleanToInt32(subscriptionInfo.Detail.Persistent),
                DeliveryAttemptIntervalSecs = subscriptionInfo.Detail.DeliveryAttemptIntervalSecs,
                MaxDeliveryAttemptCount = subscriptionInfo.Detail.MaxDeliveryAttemptCount,
                Paused = Convertor.BooleanToInt32(subscriptionInfo.Detail.Paused),
                ExpiryDateTimeUtc = subscriptionInfo.Detail.ExpiryDateTimeUtc,
                MessageValidityDurationSecs = subscriptionInfo.Detail.MessageValidityDurationSecs,
                RequestPipelineId = reqPlId,
                ResponsePipelineId = respPlId,
                SubscriberDbId = subscriberDbId
            };
            int rowCount = dbConn.ExecuteOracleSafe(dbCmd, parameters);

            long SubscriptionDbId = 0;
            if (rowCount == 1)
            {
                object prmts = new
                {
                    Id = subscriptionInfo.Detail.Id,
                    SubscriberDbId = subscriberDbId
                };
                string dbCmdDel = "delete Subscription_Filter " +
                                  "where SubscriptionDbId in (select s.DbId " +
                                  "                           from Subscription s " +
                                  "                           where s.Id             = @Id " +
                                  "                             and s.SubscriberDbId = @SubscriberDbId) ";
                dbConn.ExecuteOracleSafe(dbCmdDel, prmts);

                string dbCmdSel = "select s.DbId " +
                                  "from Subscription s " +
                                  "where s.Id = @Id " +
                                  "  and s.SubscriberDbId = @SubscriberDbId ";
                SubscriptionDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), prmts).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Subscription (Id " +
                                  "                         ,Name " +
                                  "                         ,EndpointId " +
                                  "                         ,Persistent " +
                                  "                         ,DeliveryAttemptIntervalSecs " +
                                  "                         ,MaxDeliveryAttemptCount " +
                                  "                         ,SubscriberDbId " +
                                  "                         ,Paused " +
                                  "                         ,ExpiryDateTimeUtc " +
                                  "                         ,MessageValidityDurationSecs " +
                                  "                         ,RequestPipelineId " +
                                  "                         ,ResponsePipelineId) " +
                                  "values (@Id " +
                                  "       ,@Name " +
                                  "       ,@EndpointId " +
                                  "       ,@Persistent " +
                                  "       ,@DeliveryAttemptIntervalSecs " +
                                  "       ,@MaxDeliveryAttemptCount " +
                                  "       ,@SubscriberDbId " +
                                  "       ,@Paused " +
                                  "       ,@ExpiryDateTimeUtc " +
                                  "       ,@MessageValidityDurationSecs " +
                                  "       ,@RequestPipelineId " +
                                  "       ,@ResponsePipelineId) " +
                                  "returning DbId into @DbId";

                SubscriptionDbId = dbConn.QueryReturning<long>(dbCmdIns, parameters, "DbId");
            }

            return SubscriptionDbId;
        }

        private IDictionary<string, long> EndpointsInsertUpdate(IDbConnection dbConn, long clientDbId,
            ICollection<EndpointInfo> endpoints)
        {
            IDictionary<string, long> idMap = new Dictionary<string, long>();
            foreach (EndpointInfo ei in endpoints)
            {
                this.EndpointInsertUpdate(dbConn, clientDbId, ei, idMap);
            }
            return idMap;
        }

        private void EndpointInsertUpdate(IDbConnection dbConn, long? clientDbId, EndpointInfo ei,
            IDictionary<string, long> idMap)
        {
            string dbCmd = "update Endpoint " +
                           "set Address                    = @Address " +
                           "   ,EndpointNameDbId           = @EndpointNameDbId " +
                           "   ,CustomEndpointName         = @CustomEndpointName " +
                           "   ,TransportTypeDbId          = @TransportTypeDbId " +
                           "   ,CustomTransportType        = @CustomTransportType " +
                           "   ,ExchangeTypeDbId           = @ExchangeTypeDbId " +
                           "   ,TransactionScopeOptionDbId = @TransactionScopeOptionDbId " +
                           "where Id         = @Id " +
                           "  and ClientDbId = @ClientDbId";
            object parameters = new
            {
                Address = ei.Address,
                EndpointNameDbId = (long) ei.EndpointName,
                CustomEndpointName = ei.CustomEndpointName,
                TransportTypeDbId = (long) ei.TransportType,
                CustomTransportType = (string) null,
                ExchangeTypeDbId = (long) ei.ExchangeType,
                TransactionScopeOptionDbId = (long) ei.TransactionScopeOption,
                Id = ei.Id,
                ClientDbId = clientDbId
            };
            int rowCount = dbConn.ExecuteOracleSafe(dbCmd, parameters);

            long EndpointDbId = 0;
            if (rowCount == 1)
            {
                string dbCmdSel = "select e.DbId " +
                                  "from Endpoint e " +
                                  "where e.Id         = @Id " +
                                  "  and e.ClientDbId = @ClientDbId";

                EndpointDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Id = ei.Id,
                    ClientDbId = clientDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Endpoint (Id " +
                                  "                     ,ClientDbId " +
                                  "                     ,Address " +
                                  "                     ,EndpointNameDbId " +
                                  "                     ,CustomEndpointName " +
                                  "                     ,TransportTypeDbId " +
                                  "                     ,CustomTransportType " +
                                  "                     ,ExchangeTypeDbId " +
                                  "                     ,TransactionScopeOptionDbId) " +
                                  "values (@Id " +
                                  "       ,@ClientDbId " +
                                  "       ,@Address " +
                                  "       ,@EndpointNameDbId " +
                                  "       ,@CustomEndpointName " +
                                  "       ,@TransportTypeDbId " +
                                  "       ,@CustomTransportType " +
                                  "       ,@ExchangeTypeDbId " +
                                  "       ,@TransactionScopeOptionDbId) " +
                                  "returning DbId into @DbId";

                EndpointDbId = dbConn.QueryReturning<long>(dbCmdIns, parameters, "DbId");
            }

            idMap.Add(ei.Id, EndpointDbId);
        }

        public IDictionary<string, long> FiltersInsertUpdate(IDbConnection dbConn, long clientDbId,
            ICollection<FilterInfo> filters)
        {
            FilterInfo[] sortedFilters = filters.ToArray();
            Array.Sort<FilterInfo>(sortedFilters, new FilterInfoComparerForFilterReferences(true));

            IDictionary<string, long> idMap = new Dictionary<string, long>();
            foreach (FilterInfo fi in sortedFilters)
            {
                this.FilterInsertUpdate(dbConn, clientDbId, fi, idMap);
            }

            return idMap;
        }

        public long FilterInsertUpdate(IDbConnection dbConn, long clientDbId, FilterInfo fi,
            IDictionary<string, long> idMap)
        {
            string dbCmd = "update Filter " +
                           "set FilterData     = @FilterData " +
                           "   ,CustomTypeName = @CustomTypeName " +
                           "   ,Filter1DbId    = @Filter1DbId " +
                           "   ,Filter2DbId    = @Filter2DbId " +
                           "where Id         = @Id " +
                           "  and ClientDbId = @ClientDbId";

            object parameters = new
            {
                FilterData = fi.FilterData,
                CustomTypeName = fi.CustomTypeName,
                Filter1DbId = string.IsNullOrEmpty(fi.Filter1Id) ? null : idMap[fi.Filter1Id].ToString(),
                Filter2DbId = string.IsNullOrEmpty(fi.Filter2Id) ? null : idMap[fi.Filter2Id].ToString(),
                Id = fi.Id,
                ClientDbId = clientDbId,
                FilterTypeDbId = (long) fi.FilterType
            };

            int rowCount = dbConn.ExecuteOracleSafe(dbCmd, parameters);

            long FilterDbId = 0;
            if (rowCount == 1)
            {
                string dbCmdSel = "select f.DbId " +
                                  "from Filter f " +
                                  "where f.Id         = @Id " +
                                  "  and f.ClientDbId = @ClientDbId";

                FilterDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Id = fi.Id,
                    ClientDbId = clientDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Filter (Id " +
                                  "                   ,ClientDbId " +
                                  "                   ,FilterTypeDbId " +
                                  "                   ,FilterData " +
                                  "                   ,CustomTypeName " +
                                  "                   ,Filter1DbId " +
                                  "                   ,Filter2DbId) " +
                                  "values (@Id " +
                                  "       ,@ClientDbId " +
                                  "       ,@FilterTypeDbId " +
                                  "       ,@FilterData " +
                                  "       ,@CustomTypeName " +
                                  "       ,@Filter1DbId " +
                                  "       ,@Filter2DbId) " +
                                  "returning DbId into @DbId";

                FilterDbId = dbConn.QueryReturning<long>(dbCmdIns, parameters, "DbId");
            }

            idMap.Add(fi.Id, FilterDbId);
            return FilterDbId;
        }

        private void SubscriptionFilterInsertUpdate(IDbConnection dbConn, long subscriptionDbId, long filterDbId)
        {
            string dbCmdUpd = "update Subscription_Filter " +
                              "set SubscriptionDbId = @SubscriptionDbId " +
                              "   ,FilterDbId       = @FilterDbId " +
                              "where SubscriptionDbId = @SubscriptionDbId " +
                              "  and FilterDbId       = @FilterDbId";

            object parameters = new
            {
                SubscriptionDbId = subscriptionDbId,
                FilterDbId = filterDbId
            };

            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, parameters);

            if (rowCount == 0)
            {
                string dbCmdSel = "insert into Subscription_Filter (SubscriptionDbId " +
                                  ",FilterDbId) " +
                                  "values (@SubscriptionDbId " +
                                  ",@FilterDbId)";

                dbConn.ExecuteOracleSafe(dbCmdSel, parameters);
            }
        }

        private object GetFilterDbId(string filterId, IDictionary<string, long> idMap)
        {
            if (string.IsNullOrEmpty(filterId))
            {
                return null;
            }

            return idMap[filterId];
        }

        private IDictionary<string, long> NamespaceDefinitionsInsertUpdate(IDbConnection dbConn, long clientDbId,
            IEnumerable<NamespaceInfo> namespaceDefinitions)
        {
            IDictionary<string, long> idMap = new Dictionary<string, long>();
            foreach (NamespaceInfo ni in namespaceDefinitions)
            {
                this.NamespaceDefinitionInsertUpdate(dbConn, clientDbId, ni, idMap);
            }
            return idMap;
        }

        private void NamespaceDefinitionInsertUpdate(IDbConnection dbConn, long clientDbId, NamespaceInfo ni,
            IDictionary<string, long> idMap)
        {
            string dbCmdUpd = "update NamespaceDefinition " +
                              "set Prefix    = @Prefix " +
                              "   ,Namespace = @Namespace " +
                              "where Prefix     = @Prefix " +
                              "  and ClientDbId = @ClientDbId";

            object parameters = new
            {
                Prefix = ni.Prefix,
                Namespace = ni.Namespace,
                ClientDbId = clientDbId
            };

            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, parameters);
            long namespaceDefinitionDbId = 0;

            if (rowCount == 1)
            {
                string dbCmdSel = "select n.DbId " +
                                  "from NamespaceDefinition n " +
                                  "where n.Prefix     = @Prefix " +
                                  "  and n.ClientDbId = @ClientDbId";

                namespaceDefinitionDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Prefix = ni.Prefix,
                    ClientDbId = clientDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into NamespaceDefinition (ClientDbId " +
                                  "                                ,Prefix " +
                                  "                                ,Namespace) " +
                                  "values (@ClientDbId " +
                                  "       ,@Prefix " +
                                  "       ,@Namespace) " +
                                  "returning DbId into @DbId";

                namespaceDefinitionDbId = dbConn.QueryReturning<long>(dbCmdIns, parameters, "DbId");
            }

            idMap.Add(ni.Prefix, namespaceDefinitionDbId);
        }

        public IDictionary<string, long> PipelinesInsertUpdate(IDbConnection dbConn, long clientDbId,
            ICollection<PipelineInfo> pipelines)
        {
            IDictionary<string, long> idMap = new Dictionary<string, long>();
            foreach (PipelineInfo pi in pipelines)
            {
                this.PipelineInsertUpdate(dbConn, clientDbId, pi, idMap);
            }

            return idMap;
        }

        public long PipelineInsertUpdate(IDbConnection dbConn, long clientDbId, PipelineInfo pi,
            IDictionary<string, long> idMap)
        {
            string dbCmdUpd = "update Pipeline " +
                              "set PipelineInfo = @PipelineInfo " +
                              "where Id         = @Id " +
                              "  and ClientDbId = @ClientDbId";

            var pipelineInfoByteArray = pi.GetByteArray();
            object parameters = new
            {
                PipelineInfo = pipelineInfoByteArray,
                Id = pi.Id,
                ClientDbId = clientDbId
            };

            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, parameters);
            long PipelineDbId = 0;

            if (rowCount == 1)
            {
                string dbCmdSel = "select p.DbId " +
                                  "from Pipeline p " +
                                  "where p.Id         = @Id " +
                                  "  and p.ClientDbId = @ClientDbId";

                PipelineDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Id = pi.Id,
                    ClientDbId = clientDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Pipeline (Id " +
                                  "                     ,ClientDbId " +
                                  "                     ,PipelineInfo) " +
                                  "values (@Id " +
                                  "       ,@ClientDbId " +
                                  "       ,@PipelineInfo) " +
                                  "returning DbId into @DbId";

                PipelineDbId = dbConn.QueryReturning<long>(dbCmdIns, parameters, "DbId");
            }

            idMap.Add(pi.Id, PipelineDbId);

            return PipelineDbId;
        }

        public void RemoveSubscribers(RemoveSubscribersRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);
            dbConn.Open();

            string dbCmdSel = "select s.SubscriberDbId " +
                              "from Subscriber s " +
                              "join Client c on " +
                              "     c.DbId = s.SubscriberDbId " +
                              "where c.Id = @SubscriberId ";

            foreach (string subscriberId in request.SubscriberIds)
            {
                long SubscriberDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new {SubscriberId = subscriberId}).Single();
                ClientDelete(dbConn, SubscriberDbId);
            }

            return;
        }

        public void RemoveSubscriptions(RemoveSubscriptionsRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);
            dbConn.Open();

            foreach (string subscriptionId in request.SubscriptionIds)
            {
                this.RemoveSubscription(dbConn, request.SubscriberId, subscriptionId);
            }

            return;
        }

        private void RemoveSubscription(IDbConnection dbConn, string subscriberId, string subscriptionId)
        {
            string dbCmdDelete = "delete Subscription_Filter " +
                                 "where SubscriptionDbId in (select s.DbId " +
                                 "                           from Subscription s " +
                                 "                           where s.Id = @SubscriptionId " +
                                 "                             and s.SubscriberDbId in (select c.DbId " +
                                 "                                                      from Client c " +
                                 "                                                      where c.Id = @ClientId)) ";

            dbConn.ExecuteOracleSafe(dbCmdDelete, new
            {
                ClientId = subscriberId,
                SubscriptionId = subscriptionId
            });
        }

        public SubscriberConfiguration GetSubscriberConfiguration()
        {
            SubscriberConfiguration sc = new SubscriberConfiguration();

            IDictionary<string, long> idMap = new Dictionary<string, long>();
            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();

                string dbCmdSel = "select c.DbId " +
                                  "      ,c.Id " +
                                  "      ,c.ModificationDateTimeUtc " +
                                  "from Client c " +
                                  "where c.ClientTypeDbId = 0 ";
                IEnumerable<dynamic> clientList = dbConn.Query(dbCmdSel);
                foreach (var client in clientList)
                {
                    SubscriberInfo si = new SubscriberInfo(
                        (string) client.Id,
                        modificationDateTimeUtc: (DateTime?) client.ModificationDateTimeUtc
                        );

                    try
                    {
                        idMap.Add(si.Id, (long) client.DbId);
                    }
                    catch (Exception)
                    {
                        continue;
                    }
                    sc.EvaluationElementDictionary.Add(si.Id, si);
                }

                foreach (SubscriberInfo si in sc.EvaluationElementDictionary.Values)
                {
                    long dbId = idMap[si.Id];

                    // Namespaces
                    si.NamespaceInfoDictionary = this.ReadNamespaces(dbConn, dbId);

                    // Filters
                    si.FilterInfoDictionary = this.ReadFilters(dbConn, dbId);

                    // Pipelines
                    si.PipelineInfoDictionary = this.ReadPipelines(dbConn, dbId);

                    // Endpoints
                    si.EndpointInfoDictionary = this.ReadEndpoints(dbConn, dbId);

                    // Subscriptions
                    si.InfoDictionary = this.ReadSubscriptions(dbConn, dbId, si);
                }
            }

            return sc;
        }

        private CaseInsensitiveStringDictionary<NamespaceInfo> ReadNamespaces(IDbConnection dbConn, long clientDbId)
        {
            var namespaceDictionary = new CaseInsensitiveStringDictionary<NamespaceInfo>();

            string dbCmdSel = "select n.DbId " +
                              "      ,n.Prefix " +
                              "      ,n.Namespace " +
                              "from NamespaceDefinition n " +
                              "where n.ClientDbId = @ClientDbId ";
            IEnumerable<dynamic> namespaceDefinitionList = dbConn.Query(dbCmdSel, new {ClientDbId = clientDbId});
            foreach (var namespaceDefinition in namespaceDefinitionList)
            {
                long dbId = (long) namespaceDefinition.DbId;
                NamespaceInfo ni = new NamespaceInfo()
                {
                    Prefix = (string) namespaceDefinition.Prefix,
                    Namespace = (string) namespaceDefinition.Namespace,
                };

                namespaceDictionary.Add(ni.Prefix, ni);
            }

            return namespaceDictionary;
        }

        private CaseInsensitiveStringDictionary<FilterInfo> ReadFilters(IDbConnection dbConn, long clientDbId)
        {
            var filterDictionary = new CaseInsensitiveStringDictionary<FilterInfo>();

            string dbCmdSel = "select f.DbId " +
                              "      ,f.Id " +
                              "      ,f.FilterTypeDbId " +
                              "      ,f.FilterData " +
                              "      ,f.CustomTypeName " +
                              "      ,Filter1Id = (select Id from Filter f1 where f1.DbId = f.Filter1DbId) " +
                              "      ,Filter2Id = (select Id from Filter f2 where f2.DbId = f.Filter2DbId) " +
                              "from Filter f " +
                              //"join Filter f1 on " +
                              //"     f.Filter1DbId = f1.DbId " +
                              //"join Filter f2 on " +
                              //"     f.Filter2DbId = f2.DbId " +
                              "where f.ClientDbId = @ClientDbId ";
            IEnumerable<dynamic> filterList = dbConn.Query(dbCmdSel, new {ClientDbId = clientDbId});
            foreach (var filter in filterList)
            {
                long dbId = (long) filter.DbId;
                FilterInfo fi = new FilterInfo()
                {
                    Id = (string) filter.Id,
                    FilterType = (FilterTypeEnum) (long) filter.FilterTypeDbId,
                    FilterData = filter.FilterData,
                    CustomTypeName = filter.CustomTypeName,
                    Filter1Id = filter.Filter1Id,
                    Filter2Id = filter.Filter2Id
                };

                filterDictionary.Add(fi.Id, fi);
            }

            return filterDictionary;
        }

        private CaseInsensitiveStringDictionary<PipelineInfo> ReadPipelines(IDbConnection dbConn, long clientDbId)
        {
            var pipelineDictionary = new CaseInsensitiveStringDictionary<PipelineInfo>();

            string dbCmdSel = "select p.DbId " +
                              "      ,p.Id " +
                              "      ,p.PipelineInfo " +
                              "from Pipeline p " +
                              "where p.ClientDbId = @ClientDbId ";
            IEnumerable<dynamic> pipelineList = dbConn.Query(dbCmdSel, new {ClientDbId = clientDbId});

            foreach (var pipeline in pipelineList)
            {
                byte[] pipelineInfoByteArray = (byte[]) pipeline.PipelineInfo;
                var pi = PipelineInfo.CreateFromByteArray(pipelineInfoByteArray);

                pipelineDictionary.Add(pi.Id, pi);
            }

            return pipelineDictionary;
        }

        private EndpointInfo ReadEndpoint(IDbConnection dbConn, string endpointId)
        {
            string dbCmdSel = "select e.DbId " +
                              "      ,e.Id " +
                              "      ,e.Address " +
                              "      ,e.EndpointNameDbId " +
                              "      ,e.CustomEndpointName " +
                              "      ,e.TransportTypeDbId " +
                              "      ,e.CustomTransportType " +
                              "      ,e.ExchangeTypeDbId " +
                              "      ,e.TransactionScopeOptionDbId " +
                              "from Endpoint e " +
                              "where e.Id = @EndpointId ";
            IEnumerable<dynamic> endpointList = dbConn.Query(dbCmdSel, new { EndpointId = endpointId });
            foreach (var endpoint in endpointList)
            {
                EndpointInfo ei = new EndpointInfo()
                {
                    Id = (string)endpoint.Id,
                    Address = (string)endpoint.Address,
                    ExchangeType = (ExchangeTypeEnum)(long)endpoint.ExchangeTypeDbId,
                    EndpointName = (EndpointNameEnum)(long)endpoint.EndpointNameDbId,
                    CustomEndpointName = endpoint.CustomEndpointName,
                    TransportType = (TransportTypeEnum)(long)endpoint.TransportTypeDbId,
                    TransactionScopeOption = (TransactionScopeOption)(long)endpoint.TransactionScopeOptionDbId,
                };

                return ei;
            }

            return null;
        }

        private CaseInsensitiveStringDictionary<EndpointInfo> ReadEndpoints(IDbConnection dbConn, long clientDbId)
        {
            var endpointDictionary = new CaseInsensitiveStringDictionary<EndpointInfo>();

            string dbCmdSel = "select e.DbId " +
                              "      ,e.Id " +
                              "      ,e.Address " +
                              "      ,e.EndpointNameDbId " +
                              "      ,e.CustomEndpointName " +
                              "      ,e.TransportTypeDbId " +
                              "      ,e.CustomTransportType " +
                              "      ,e.ExchangeTypeDbId " +
                              "      ,e.TransactionScopeOptionDbId " +
                              "from Endpoint e " +
                              "where e.ClientDbId = @ClientDbId ";
            IEnumerable<dynamic> endpointList = dbConn.Query(dbCmdSel, new {ClientDbId = clientDbId});
            foreach (var endpoint in endpointList)
            {
                long dbId = (long) endpoint.DbId;
                EndpointInfo ei = new EndpointInfo()
                {
                    Id = (string) endpoint.Id,
                    Address = (string) endpoint.Address,
                    ExchangeType = (ExchangeTypeEnum) (long) endpoint.ExchangeTypeDbId,
                    EndpointName = (EndpointNameEnum) (long) endpoint.EndpointNameDbId,
                    CustomEndpointName = endpoint.CustomEndpointName,
                    TransportType = (TransportTypeEnum) (long) endpoint.TransportTypeDbId,
                    TransactionScopeOption = (TransactionScopeOption) (long) endpoint.TransactionScopeOptionDbId,
                };

                endpointDictionary.Add(ei.Id, ei);
            }

            return endpointDictionary;
        }

        private CaseInsensitiveStringDictionary<SubscriptionInfo> ReadSubscriptions(IDbConnection dbConn,
            long subscriberDbId, SubscriberInfo si)
        {
            var subscriptionDetailDictionary = new CaseInsensitiveStringDictionary<SubscriptionDetail>();

            IDictionary<string, long> idMap = new Dictionary<string, long>();

            string dbCmdSel = "select s.DbId " +
                              "      ,s.Id " +
                              "      ,s.Name " +
                              "      ,s.EndpointId " +
                              "      ,s.Persistent " +
                              "      ,s.Paused " +
                              "      ,s.ExpiryDateTimeUtc " +
                              "      ,s.MessageValidityDurationSecs " +
                              "      ,s.DeliveryAttemptIntervalSecs " +
                              "      ,s.MaxDeliveryAttemptCount " +
                              "      ,s.RequestPipelineId " +
                              "      ,s.ResponsePipelineId " +
                              "from Subscription s " +
                              "where s.SubscriberDbId = @SubscriberDbId " +
                              " and (s.ExpiryDateTimeUtc is null " +
                              "   or s.ExpiryDateTimeUtc > sysutcdatetime()) ";
            IEnumerable<dynamic> subscriptionList = dbConn.Query(dbCmdSel, new {SubscriberDbId = subscriberDbId});

            foreach (var subscription in subscriptionList)
            {
                long dbId = (long) subscription.DbId;

                var requestPipelineId = subscription.RequestPipelineId;
                var responsePipelineId = subscription.ResponsePipelineId;

                SubscriptionDetail sd = new SubscriptionDetail(
                    si.Id,
                    (string)subscription.Id,
                    (string)subscription.Name,
                    (string)subscription.EndpointId,
                    (bool) subscription.Persistent,
                    (long?) subscription.MessageValidityDurationSecs,
                    subscription.DeliveryAttemptIntervalSecs,
                    subscription.MaxDeliveryAttemptCount,
                    (bool) subscription.Paused,
                    subscription.ExpiryDateTimeUtc,
                    requestPipelineId,
                    responsePipelineId
                    );

                idMap.Add(sd.Id, dbId);
                subscriptionDetailDictionary.Add(sd.Id, sd);
            }

            var subscriptionDictionary = new CaseInsensitiveStringDictionary<SubscriptionInfo>();
            foreach (SubscriptionDetail sd in subscriptionDetailDictionary.Values)
            {
                long dbId = idMap[sd.Id];

                var fis = this.ReadSubscriptionFilters(dbConn, dbId);

                SubscriptionInfo sci = new SubscriptionInfo(si, sd, fis);
                subscriptionDictionary.Add(sci.Detail.Id, sci);
            }

            return subscriptionDictionary;
        }

        private IEnumerable<string> ReadSubscriptionFilters(IDbConnection dbConn, long subscriptionDbId)
        {
            IList<string> subscriptionFilters = new List<string>();

            string dbCmdSel = "select distinct f.Id " +
                              "from Filter f " +
                              "join Subscription_Filter sf on " +
                              "     f.DbId = sf.FilterDbId " +
                              "where sf.SubscriptionDbId = @SubscriptionDbId ";
            IEnumerable<dynamic> filterSubscrFilterList = dbConn.Query(dbCmdSel,
                new {SubscriptionDbId = subscriptionDbId});

            foreach (var filterSubscrFilter in filterSubscrFilterList)
            {
                string filterId = (string) filterSubscrFilter.Id;
                subscriptionFilters.Add(filterId);
            }

            return subscriptionFilters;
        }

        public void AddOrUpdatePublisherConfiguration(PublisherConfiguration publisherConfiguration)
        {
            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();

                foreach (PublisherInfo pi in publisherConfiguration.EvaluationElementDictionary.Values.ToArray())
                {
                    // Publisher
                    long publisherDbId = this.PublisherInsertUpdate(dbConn, pi.Id);

                    // Endpoints
                    IDictionary<string, long> eiIdMap = this.EndpointsInsertUpdate(dbConn, publisherDbId,
                        pi.EndpointInfoDictionary.Values);

                    // Pipelines
                    IDictionary<string, long> piIdMap = this.PipelinesInsertUpdate(dbConn, publisherDbId,
                        pi.PipelineInfoDictionary.Values);

                    // Responses
                    IDictionary<string, long> riIdMap = this.ResponsesInsertUpdate(dbConn, publisherDbId,
                        pi.ResponseInfoDictionary.Values);

                    // ReceivePortInfos
                    foreach (KeyValuePair<string, ReceivePortInfo> kvp in pi.InfoDictionary)
                    {
                        // ReceivePort
                        ReceivePortInfo pci = kvp.Value;
                        long receivePortDbId = this.ReceivePortInsertUpdate(dbConn, publisherDbId, pci);
                    }
                }
            }

            return;
        }

        private long PublisherInsertUpdate(IDbConnection dbConn, string publisherId)
        {
            long ClientDbId = ClientInsertUpdate(dbConn, publisherId);

            string dbCmdUpd = "update Publisher " +
                              "set PublisherDbId = @ClientDbId " +
                              "where PublisherDbId = @ClientDbId ";
            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, new {ClientDbId = ClientDbId});

            long PublisherDbId = 0;
            if (rowCount == 1)
            {
                PublisherDbId = ClientDbId;
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Publisher (PublisherDbId) " +
                                  "values (@ClientDbId) "; // " +
                //"select scope_identity()";
                //PublisherDbId = dbConn.Query<long>(dbCmdIns, new { ClientDbId = ClientDbId }).Single();
                dbConn.ExecuteOracleSafe(dbCmdIns, new {ClientDbId = ClientDbId});
                PublisherDbId = ClientDbId;
            }

            return PublisherDbId;
        }

        private long ReceivePortInsertUpdate(IDbConnection dbConn, long publisherDbId,
            ReceivePortInfo receivePortInfo)
        {
            string dbCmdUpd = "update ReceivePort " +
                              "set Name                    = @Name " +
                              "   ,Paused                  = @Paused " +
                              "   ,EndpointId              = @EndpointId " +
                              "   ,ExpiryDateTimeUtc       = @ExpiryDateTimeUtc " +
                              "   ,ResponseId              = @ResponseId " +
                              "   ,GenerateDefaultResponse = @GenerateDefaultResponse " +
                              "   ,RequestPipelineId       = @RequestPipelineId " +
                              "   ,ResponsePipelineId      = @ResponsePipelineId " +
                              "where Id            = @Id " +
                              "  and PublisherDbId = @PublisherDbId ";

            string EndpointId = null;
            if (!string.IsNullOrEmpty(receivePortInfo.Detail.EndpointId))
            {
                EndpointId = receivePortInfo.Detail.EndpointId;
            }

            string RequestPipelineId = null;
            if (!string.IsNullOrEmpty(receivePortInfo.Detail.RequestPipelineId))
            {
                RequestPipelineId = receivePortInfo.Detail.RequestPipelineId;
            }

            string ResponsePipelineId = null;
            if (!string.IsNullOrEmpty(receivePortInfo.Detail.ResponsePipelineId))
            {
                ResponsePipelineId = receivePortInfo.Detail.ResponsePipelineId;
            }

            object prmts = new
            {
                Id = receivePortInfo.Detail.Id,
                Name = receivePortInfo.Detail.Name,
                Paused = Convertor.BooleanToInt32(receivePortInfo.Detail.Paused),
                EndpointId = EndpointId,
                ExpiryDateTimeUtc = receivePortInfo.Detail.ExpiryDateTimeUtc,
                ResponseId = (string) null,
                GenerateDefaultResponse = Convertor.BooleanToInt32(receivePortInfo.GenerateDefaultResponse),
                RequestPipelineId = RequestPipelineId,
                ResponsePipelineId = ResponsePipelineId,
                PublisherDbId = publisherDbId
            };

            long ReceivePortDbId = 0;
            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, prmts);
            if (rowCount == 1)
            {
                string dbCmdSel = "select r.DbId " +
                                  "from ReceivePort r " +
                                  "where r.Id            = @Id " +
                                  "  and r.PublisherDbId = @PublisherDbId ";
                ReceivePortDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Id = receivePortInfo.Detail.Id,
                    PublisherDbId = publisherDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into ReceivePort (Id " +
                                  "                        ,Name " +
                                  "                        ,PublisherDbId " +
                                  "                        ,EndpointId " +
                                  "                        ,Paused " +
                                  "                        ,ExpiryDateTimeUtc " +
                                  "                        ,ResponseId " +
                                  "                        ,GenerateDefaultResponse " +
                                  "                        ,RequestPipelineId " +
                                  "                        ,ResponsePipelineId) " +
                                  "values (@Id " +
                                  "       ,@Name " +
                                  "       ,@PublisherDbId " +
                                  "       ,@EndpointId " +
                                  "       ,@Paused " +
                                  "       ,@ExpiryDateTimeUtc " +
                                  "       ,@ResponseId " +
                                  "       ,@GenerateDefaultResponse " +
                                  "       ,@RequestPipelineId " +
                                  "       ,@ResponsePipelineId) " +
                                  "returning DbId into @DbId";

                ReceivePortDbId = dbConn.QueryReturning<long>(dbCmdIns, prmts, "DbId");
            }

            return ReceivePortDbId;
        }

        private IDictionary<string, long> ResponsesInsertUpdate(IDbConnection dbConn, long publisherDbId,
            ICollection<ResponseInfo> responses)
        {
            IDictionary<string, long> idMap = new Dictionary<string, long>();
            foreach (ResponseInfo ri in responses)
            {
                this.ResponseInsertUpdate(dbConn, publisherDbId, ri, idMap);
            }

            return idMap;
        }

        private long ResponseInsertUpdate(IDbConnection dbConn, long publisherDbId, ResponseInfo ri,
            IDictionary<string, long> idMap)
        {
            string dbCmdUpd = "update Response " +
                              "set ResponseAction = @ResponseAction " +
                              "   ,BufferSize     = @BufferSize " +
                              "   ,ResponseXml    = @ResponseXml " +
                              "where Id            = @Id" +
                              "  and PublisherDbId = @PublisherDbId";

            object prmts = new
            {
                ResponseAction = (string) ri.ResponseAction,
                BufferSize = ri.BufferSize,
                ResponseXml = ri.ResponseXml,
                Id = ri.Id,
                PublisherDbId = publisherDbId
            };

            long ResponseDbId = 0;
            int rowCount = dbConn.ExecuteOracleSafe(dbCmdUpd, prmts);
            if (rowCount == 1)
            {
                string dbCmdSel = "select r.DbId " +
                                  "from Response r " +
                                  "where r.Id            = @Id " +
                                  "  and r.PublisherDbId = @PublisherDbId ";

                ResponseDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new
                {
                    Id = ri.Id,
                    PublisherDbId = publisherDbId
                }).Single();
            }
            else if (rowCount == 0)
            {
                string dbCmdIns = "insert into Response (Id " +
                                  "                     ,PublisherDbId " +
                                  "                     ,ResponseAction " +
                                  "                     ,BufferSize " +
                                  "                     ,ResponseXml) " +
                                  "values (@Id " +
                                  "       ,@PublisherDbId " +
                                  "       ,@ResponseAction " +
                                  "       ,@BufferSize " +
                                  "       ,@ResponseXml) " +
                                  "returning DbId into @DbId";

                ResponseDbId = dbConn.QueryReturning<long>(dbCmdIns, prmts, "DbId");
            }

            idMap.Add(ri.Id, ResponseDbId);
            return ResponseDbId;
        }

        public void RemovePublishers(RemovePublishersRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);
            dbConn.Open();

            string dbCmdSel = "select p.PublisherDbId " +
                              "from Publisher p " +
                              "join Client c on " +
                              "     c.DbId = p.PublisherDbId " +
                              "where c.Id = @PublisherId ";

            foreach (string publisherId in request.PublisherIds)
            {
                long PublisherDbId = dbConn.Query<long>(dbCmdSel.Replace("@", ":"), new {PublisherId = publisherId}).Single();
                ClientDelete(dbConn, PublisherDbId);
            }

            return;
        }

        public void RemoveReceivePorts(RemoveReceivePortsRequest request)
        {
            IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString);
            dbConn.Open();

            foreach (string ReceivePortId in request.ReceivePortIds)
            {
                this.RemoveReceivePort(dbConn, request.PublisherId, ReceivePortId);
            }

            return;
        }

        private void RemoveReceivePort(IDbConnection dbConn, string publisherId, string receivePortId)
        {
            string dbCmdSel = "select r.DbId " +
                              "from ReceivePort r " +
                              "join Client c on " +
                              "     c.DbId = r.PublisherDbId " +
                              "where c.Id = @ClientId " +
                              "  and r.Id = @ReceivePortId ";

            object prmts = new
            {
                ClientId = publisherId,
                ReceivePortId = receivePortId
            };

            dbConn.ExecuteOracleSafe(dbCmdSel, prmts);
        }

        private CaseInsensitiveStringDictionary<ReceivePortInfo> ReadReceivePorts(IDbConnection dbConn,
            long publisherDbId, PublisherInfo pi)
        {
            var receivePortDictionary = new CaseInsensitiveStringDictionary<ReceivePortInfo>();

            IDictionary<string, long> idMap = new Dictionary<string, long>();

            string dbCmdSel = "select r.DbId " +
                              "      ,r.Id " +
                              "      ,r.Name " +
                              "      ,r.EndpointId" +
                              "      ,r.Paused " +
                              "      ,r.ExpiryDateTimeUtc " +
                              "      ,r.ResponseId " +
                              "      ,r.GenerateDefaultResponse " +
                              "      ,r.RequestPipelineId " +
                              "      ,r.ResponsePipelineId " +
                              "from ReceivePort r " +
                              "where r.PublisherDbId = @PublisherDbId " +
                              " and (r.ExpiryDateTimeUtc is null " +
                              "   or r.ExpiryDateTimeUtc = sysutcdatetime()) ";
            IEnumerable<dynamic> receivePortList = dbConn.Query(dbCmdSel, new {PublisherDbId = publisherDbId});

            foreach (var receivePort in receivePortList)
            {
                long dbId = (long) receivePort.DbId;

                var endpointId = receivePort.EndpointId;
                var requestPipelineId = receivePort.RequestPipelineId;
                var responsePipelineId = receivePort.ResponsePipelineId;

                ReceivePortDetail pd = new ReceivePortDetail(
                    pi.Id,
                    (string) receivePort.Id,
                    (string)receivePort.Name,
                    endpointId,
                    (bool) receivePort.Paused,
                    receivePort.ExpiryDateTimeUtc,
                    requestPipelineId,
                    responsePipelineId
                    );

                var responseId = receivePort.ResponseId;
                ReceivePortInfo ai = new ReceivePortInfo(
                    pi,
                    pd,
                    responseId,
                    (bool) receivePort.GenerateDefaultResponse);
                idMap.Add(ai.Detail.Id, dbId);
                receivePortDictionary.Add(ai.Detail.Id, ai);
            }

            return receivePortDictionary;
        }

        public PublisherConfiguration GetPublisherConfiguration()
        {
            PublisherConfiguration pc = new PublisherConfiguration();

            IDictionary<string, long> idMap = new Dictionary<string, long>();

            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();
                string dbCmdSel = "select c.DbId " +
                                  "      ,c.Id " +
                                  "      ,c.ModificationDateTimeUtc " +
                                  "from Client c " +
                                  "where c.ClientTypeDbId = 1 ";
                IEnumerable<dynamic> clientList = dbConn.Query(dbCmdSel);

                foreach (var client in clientList)
                {
                    long dbId = (long) client.DbId;
                    PublisherInfo pi = new PublisherInfo(
                        (string) client.Id,
                        modificationDateTimeUtc: (DateTime) client.ModificationDateTimeUtc
                        );

                    idMap.Add(pi.Id, dbId);
                    pc.EvaluationElementDictionary.Add(pi.Id, pi);
                }

                foreach (PublisherInfo pi in pc.EvaluationElementDictionary.Values)
                {
                    long dbId = idMap[pi.Id];

                    // Endpoints
                    pi.EndpointInfoDictionary = this.ReadEndpoints(dbConn, dbId);

                    // Pipelines
                    pi.PipelineInfoDictionary = this.ReadPipelines(dbConn, dbId);

                    // Responses
                    pi.ResponseInfoDictionary = this.ReadPublisherResponses(dbConn, dbId);

                    // ReceivePorts
                    pi.InfoDictionary = this.ReadReceivePorts(dbConn, dbId, pi);
                }
            }

            return pc;
        }

        public SubscriberInfo GetSubscriber(GetSubscriberRequest request)
        {
            if (string.IsNullOrEmpty(request.SubscriberId))
                throw new ArgumentException("request should contain a subscriber id", "request");
           
            SubscriberInfo si = null;
            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();
                string dbCmdSel = "select DbId, Id, ModificationDateTimeUtc from Client c where c.ClientTypeDbId = 0 and c.Id = @subscriberId";
                object prmts = new
                {
                    subscriberId = request.SubscriberId
                };

                long dbId = 0;
                IEnumerable<dynamic> clientList = dbConn.Query(dbCmdSel, prmts);
                foreach (var client in clientList)
                {
                    dbId = (long)client.DbId;
                    si = new SubscriberInfo(
                            (string)client.Id,
                            modificationDateTimeUtc: (DateTime)client.ModificationDateTimeUtc
                            );
                }
                if (si == null)
                    return null;

                // Namespaces
                si.NamespaceInfoDictionary = this.ReadNamespaces(dbConn, dbId);

                // Filters
                si.FilterInfoDictionary = this.ReadFilters(dbConn, dbId);

                // Transformations
                si.PipelineInfoDictionary = this.ReadPipelines(dbConn, dbId);

                // Endpoints
                si.EndpointInfoDictionary = this.ReadEndpoints(dbConn, dbId);

                // Subscriptions
                si.InfoDictionary = this.ReadSubscriptions(dbConn, dbId, si);
            }

            return si;
        }

        private CaseInsensitiveStringDictionary<ResponseInfo> ReadPublisherResponses(IDbConnection dbConn,
            long publisherDbId)
        {
            var responseDictionary = new CaseInsensitiveStringDictionary<ResponseInfo>();

            string dbCmdSel = "select r.DbId " +
                              "      ,r.Id " +
                              "      ,r.ResponseAction " +
                              "      ,r.BufferSize " +
                              "      ,r.ResponseXml " +
                              "from Response r " +
                              "where r.PublisherDbId = @PublisherDbId";
            IEnumerable<dynamic> responseList = dbConn.Query(dbCmdSel, new {PublisherDbId = publisherDbId});

            foreach (var response in responseList)
            {
                long dbId = (long) response.DbId;
                ResponseInfo ri = new ResponseInfo(
                    (string) response.Id,
                    (string) response.ResponseAction,
                    (string) response.ResponseXml,
                    (int) response.BufferSize);

                responseDictionary.Add(ri.Id, ri);
            }

            return responseDictionary;
        }

        public InternalConfiguration GetInternalConfiguration()
        {
            string dbCmdSel = "select top 1 " +
                                    "ic.ReinitializationIntervalMs, " +
                                    "ic.ReinitLockTimeoutMs, " +
                                    "ic.CorrelationTimeoutMs, " +
                                    "ic.PerformanceCounterUpdateIntervalMs, " +
                                    "ic.MessageTimeoutMs, " +
                                    "ic.ServiceHostOpenTimeoutMs, " +
                                    "ic.MaxDegreeOfParallelism, " +
                                    "ic.IsolationLevel, " +
                                    "ic.TransactionTimeoutMs, " +
                                    "ic.DefaultTransientDeliveryAttemptIntervalSecs, " +
                                    "ic.DefaultTransientMaxDeliveryAttemptCount, " +
                                    "ic.DefaultMaxMessageBufferSize, " +
                                    "ic.DebugMessageLoggingEnabled, " +
                                    "ic.BufferManagerMaxPoolSize, " +
                                    "ic.BufferManagerMaxBufferSize, " +
                                    "ic.MessageFragmentSize, " +
                                    "ic.PollingBatchSize, " +
                                    "ic.PollingIntervalMs, " +
                                    "ic.DeliveryTimeoutMs, " +
                                    "ic.DefaultPersistentDeliveryAttemptIntervalSecs, " +
                                    "ic.DefaultPersistentMaxDeliveryAttemptCount, " +
                                    "ic.ContentFolder, " +
                                    "ic.CustomAssemblyFolder, " +
                                    "ic.RequestPersistenceEndpointId, " +
                                    "ic.ResponsePersistenceEndpointId, " +
                                    "ic.LastModified " +
                              "from InternalConfiguration ic " +
                              "order by ic.LastModified desc";

            string requestPersistenceEndpointId = null;
            string responsePersistenceEndpointId = null;

            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();
                IEnumerable<dynamic> responseList = dbConn.Query(dbCmdSel);

                var response = responseList.FirstOrDefault();

                InternalConfiguration configuration = new InternalConfiguration();
                if (response != null)
                {
                    configuration.CorrelationTimeoutMs = (int)response.CorrelationTimeoutMs;
                    configuration.DebugMessageLoggingEnabled = (bool)response.DebugMessageLoggingEnabled;
                    configuration.DefaultMaxMessageBufferSize = (int)response.DefaultMaxMessageBufferSize;
                    configuration.DefaultTransientDeliveryAttemptIntervalSecs = (int)response.DefaultTransientDeliveryAttemptIntervalSecs;
                    configuration.DefaultTransientMaxDeliveryAttemptCount = (int)response.DefaultTransientMaxDeliveryAttemptCount;
                    configuration.IsolationLevel = (IsolationLevelEnum)response.IsolationLevel;
                    configuration.MaxDegreeOfParallelism = (int)response.MaxDegreeOfParallelism;
                    configuration.MessageTimeoutMs = (int)response.MessageTimeoutMs;
                    configuration.PerformanceCounterUpdateIntervalMs = (int)response.PerformanceCounterUpdateIntervalMs;
                    configuration.ReinitializationIntervalMs = (int)response.ReinitializationIntervalMs;
                    configuration.ReinitLockTimeoutMs = (int)response.ReinitLockTimeoutMs;
                    configuration.ServiceHostOpenTimeoutMs = (int)response.ServiceHostOpenTimeoutMs;
                    configuration.BufferManagerMaxPoolSize = (int)response.BufferManagerMaxPoolSize;
                    configuration.BufferManagerMaxBufferSize = (int)response.BufferManagerMaxBufferSize;
                    configuration.MessageFragmentSize = (int)response.MessageFragmentSize;
                    configuration.PollingBatchSize = (int)response.PollingBatchSize;
                    configuration.PollingIntervalMs = (int)response.PollingIntervalMs;
                    configuration.DeliveryTimeoutMs = (int)response.DeliveryTimeoutMs;
                    configuration.DefaultPersistentDeliveryAttemptIntervalSecs = (int)response.DefaultPersistentDeliveryAttemptIntervalSecs;
                    configuration.DefaultPersistentMaxDeliveryAttemptCount = (int)response.DefaultPersistentMaxDeliveryAttemptCount;
                    configuration.ContentFolder = (string)response.ContentFolder;
                    configuration.CustomAssemblyFolder = (string)response.CustomAssemblyFolder;
                    requestPersistenceEndpointId = (string)response.RequestPersistenceEndpointId;
                    responsePersistenceEndpointId = (string)response.ResponsePersistenceEndpointId;
                    configuration.TransactionTimeoutMs = (int)response.TransactionTimeoutMs;
                }

                if (!string.IsNullOrEmpty(requestPersistenceEndpointId))
                {
                    configuration.RequestPersistenceEndpoint = this.ReadEndpoint(dbConn, requestPersistenceEndpointId);
                }
                if (!string.IsNullOrEmpty(responsePersistenceEndpointId))
                {
                    configuration.ResponsePersistenceEndpoint = this.ReadEndpoint(dbConn, responsePersistenceEndpointId);
                }

                return configuration;
            }
        }

        public void SetInternalConfiguration(InternalConfiguration configuration)
        {
            string dbCmdIns =
                "delete from InternalConfiguration " +
                "insert into InternalConfiguration " +
                    "(ReinitializationIntervalMs, " +
                    "ReinitLockTimeoutMs, " +
                    "CorrelationTimeoutMs, " +
                    "PerformanceCounterUpdateIntervalMs, " +
                    "MessageTimeoutMs, " +
                    "ServiceHostOpenTimeoutMs, " +
                    "MaxDegreeOfParallelism, " +
                    "IsolationLevel, " +
                    "TransactionTimeoutMs, " +
                    "DefaultTransientDeliveryAttemptIntervalSecs, " +
                    "DefaultTransientMaxDeliveryAttemptCount, " +
                    "DefaultMaxMessageBufferSize, " +
                    "DebugMessageLoggingEnabled, " +
                    "BufferManagerMaxPoolSize, " +
                    "BufferManagerMaxBufferSize, " +
                    "MessageFragmentSize, " +
                    "PollingBatchSize, " +
                    "PollingIntervalMs, " +
                    "DeliveryTimeoutMs, " +
                    "DefaultPersistentDeliveryAttemptIntervalSecs, " +
                    "DefaultPersistentMaxDeliveryAttemptCount, " +
                    "ContentFolder, " +
                    "CustomAssemblyFolder, " +
                    "RequestPersistenceEndpointId, " +
                    "ResponsePersistenceEndpointId, " +
                    "LastModified) " +
                "values " +
                    "(@ReinitializationIntervalMs, " +
                    "@ReinitLockTimeoutMs, " +
                    "@CorrelationTimeoutMs, " +
                    "@PerformanceCounterUpdateIntervalMs, " +
                    "@MessageTimeoutMs, " +
                    "@ServiceHostOpenTimeoutMs, " +
                    "@MaxDegreeOfParallelism, " +
                    "@IsolationLevel, " +
                    "@TransactionTimeoutMs, " +
                    "@DefaultTransientDeliveryAttemptIntervalSecs, " +
                    "@DefaultTransientMaxDeliveryAttemptCount, " +
                    "@DefaultMaxMessageBufferSize, " +
                    "@DebugMessageLoggingEnabled, " +
                    "@BufferManagerMaxPoolSize, " +
                    "@BufferManagerMaxBufferSize, " +
                    "@MessageFragmentSize, " +
                    "@PollingBatchSize, " +
                    "@PollingIntervalMs, " +
                    "@DeliveryTimeoutMs, " +
                    "@DefaultPersistentDeliveryAttemptIntervalSecs, " +
                    "@DefaultPersistentMaxDeliveryAttemptCount, " +
                    "@ContentFolder, " +
                    "@CustomAssemblyFolder, " +
                    "@RequestPersistenceEndpointId, " +
                    "@ResponsePersistenceEndpointId, " +
                    "@LastModified) ";

            string requestPersistenceEndpointId = null;
            if (configuration.RequestPersistenceEndpoint != null)
            {
                requestPersistenceEndpointId = configuration.RequestPersistenceEndpoint.Id;
            }

            string responsePersistenceEndpointId = null;
            if (configuration.ResponsePersistenceEndpoint != null)
            {
                responsePersistenceEndpointId = configuration.ResponsePersistenceEndpoint.Id;
            }

            object prmts = new
            {
                ReinitializationIntervalMs = configuration.ReinitializationIntervalMs,
                ReinitLockTimeoutMs = configuration.ReinitLockTimeoutMs,
                CorrelationTimeoutMs = configuration.CorrelationTimeoutMs,
                PerformanceCounterUpdateIntervalMs = configuration.PerformanceCounterUpdateIntervalMs,
                MessageTimeoutMs = configuration.MessageTimeoutMs,
                ServiceHostOpenTimeoutMs = configuration.ServiceHostOpenTimeoutMs,
                MaxDegreeOfParallelism = configuration.MaxDegreeOfParallelism,
                IsolationLevel = configuration.IsolationLevel,
                TransactionTimeoutMs = configuration.TransactionTimeoutMs,
                DefaultTransientDeliveryAttemptIntervalSecs = configuration.DefaultTransientDeliveryAttemptIntervalSecs,
                DefaultTransientMaxDeliveryAttemptCount = configuration.DefaultTransientMaxDeliveryAttemptCount,
                DefaultMaxMessageBufferSize = configuration.DefaultMaxMessageBufferSize,
                DebugMessageLoggingEnabled = configuration.DebugMessageLoggingEnabled,
                BufferManagerMaxPoolSize = configuration.BufferManagerMaxPoolSize,
                BufferManagerMaxBufferSize = configuration.BufferManagerMaxBufferSize,
                MessageFragmentSize = configuration.MessageFragmentSize,
                PollingBatchSize = configuration.PollingBatchSize,
                PollingIntervalMs = configuration.PollingIntervalMs,
                DeliveryTimeoutMs = configuration.DeliveryTimeoutMs,
                DefaultPersistentDeliveryAttemptIntervalSecs = configuration.DefaultPersistentDeliveryAttemptIntervalSecs,
                DefaultPersistentMaxDeliveryAttemptCount = configuration.DefaultPersistentMaxDeliveryAttemptCount,
                ContentFolder = configuration.ContentFolder,
                CustomAssemblyFolder = configuration.CustomAssemblyFolder,
                RequestPersistenceEndpointId = requestPersistenceEndpointId,
                ResponsePersistenceEndpointId = responsePersistenceEndpointId,
                LastModified = DateTime.UtcNow
            };

            using (IDbConnection dbConn = new OracleConnection(this.Configuration.ConnectionString))
            {
                dbConn.Open();

                if (configuration.RequestPersistenceEndpoint != null)
                {
                    this.EndpointInsertUpdate(dbConn, null, configuration.RequestPersistenceEndpoint, null);
                }
                if (configuration.ResponsePersistenceEndpoint != null)
                {
                    this.EndpointInsertUpdate(dbConn, null, configuration.ResponsePersistenceEndpoint, null);
                }

                int rowCount = dbConn.Execute(dbCmdIns, prmts);
            }
        }
    }
}